﻿using System;
using System.Data;
using System.Data.SqlClient;
using ZLDC.Common.Estate;

namespace ZLDC.DAL.Estate
{
    public class T_s_pdmDAL
    {
                                        public static DataTable GetT_s_pdmParentMenu()
        {
            DataTable dt = null;
            DataSet ds = null;
            string strSql = "select distinct(mdl) from zldc_t_s_pdm where isMenu=1 and IsActive=1 and mdl in('报表管理','人事管理')";
            
                        using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

                                                        public static DataTable GetPdmListByEmp(string EmpID, string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@EmpID", SqlDbType.Int, 10),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = EmpID;
            parameters[1].Value = strWhere;
            
            DataTable dt = null;
            using (_Database db = new _Database("estate"))
            {
                dt = db.GetDataTable("zldc_GetAllPdmByRoleIDS", parameters);
            }

            return dt;
        }

                                        public static DataTable GetT_s_pdmAllParentMenu(string strWhere)
        {
            DataTable dt = null;
            DataSet ds = null;
            string strSql = "select distinct(mdl) from zldc_t_s_pdm ";
            if (strWhere.Length > 0)
                strSql += strWhere;

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

                                                public static DataTable GetT_s_pdmByMdl(string strMdl)
        {
            DataTable dt = null;
            DataSet ds = null;

            string strSql = "select * from zldc_t_s_pdm where isMenu=1 and IsActive=1 and mdl='" + strMdl.Trim() + "' order by OrderNo ";

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

                                                public static DataTable GetT_s_pdmList(string strWhere)
        {
            DataTable dt = null;
            DataSet ds = null;

            string strSql = "select * from zldc_t_s_pdm ";
            if (strWhere.Trim() != "")
                strSql += " where " + strWhere;

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

                                         public static DataTable GetPagerT_s_RoleList(string strWhere, int currentPage, int pagesize, out int recordcount)
        {
            DataTable dt = null;
            DataSet ds = null;

            recordcount = 0;

            int startRow = (currentPage - 1) * pagesize + 1;
            string strSql = " select count(id) as counts from zldc_t_s_role " + strWhere;
            strSql += " select * from (select (ROW_NUMBER() OVER (ORDER BY id asc )) AS Row ,* from zldc_t_s_role " + strWhere + ") as A";
            strSql += " where A.Row between " + startRow + " and " + currentPage * pagesize + "";

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            if (ds != null && ds.Tables.Count > 0)
            {
                recordcount = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
                dt = ds.Tables[1];
            }

            return dt;                       
        }

                                                public static void UpdateRolePdm(string RoleID, string pdm)
        {
            string strSql = "Update zldc_t_s_Role set Pdm='" + pdm + "' where ID=" + RoleID;

            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(strSql);
            }
        }

                                                public static DataTable GetRoleList(string strWhere)
        {
            DataTable dt = null;
            DataSet ds = null;

            string strSql = " select * from zldc_t_s_Role ";
            if (strWhere.Length > 0)
                strSql += strWhere;

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

                                                public static void InsertRole(string Name, string dsc,string RoleCode,string pdm)
        {
            string strSql = " insert into zldc_t_s_Role(name,dsc,pdm,RoleCode) values('" + Name + "','" + dsc + "','" + pdm + "','" + RoleCode + "')";

            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(strSql);
            }
        }

                                                        public static void UpdateRole(string Name, string dsc,string RoleCode, string RoleID)
        {
            string strSql = " update zldc_t_s_Role set Name='" + Name + "',dsc='" + dsc + "', RoleCode='" + RoleCode + "' where id=" + RoleID;

            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(strSql);
            }
        }

                                        public static void DeleteRole(string RoleID)
        {
            string strSql = " delete from zldc_t_s_Role where id=" + RoleID;

            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(strSql);
            }
        }

                                        public static void DeleteT_s_Role_EmployeeByRoleID(string RoleID)
        {
            string strSql = " delete from zldc_t_s_Role_Employee where RoleID=" + RoleID;

            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(strSql);
            }
        }

                                                public static void DeleteT_s_Role_EmployeeByRoleIDandEmpID(string RoleID,string EmpID)
        {
            string strSql = " delete from zldc_t_s_Role_Employee where RoleID=" + RoleID + " and EmpID=" + EmpID;

            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(strSql);
            }
        }

                                                public static void DeleteT_s_Employee_DeptByEmpIDandDeptID(string EmpID, string DeptID)
        {
            string strSql = " delete from t_s_Employee_Dept where EmpID='" + EmpID + "' and DeptID='" + DeptID+"'";

            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(strSql);
            }
        }

                                                public static void InsertT_s_Role_Employee(string RoleID, string EmpID)
        {
            string strSql=" Insert into zldc_t_s_Role_Employee(RoleID,EmpID) values('"+RoleID+"','"+EmpID+"')";

            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(strSql);
            }
        }

        public static void CopyRole(string RoleID)
        {
            string strSql = "insert into zldc_t_s_role(name,dsc,pdm) " +
                          "select name,dsc,pdm from t_s_role where Id=" + RoleID;

            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(strSql);
            }
        }


                                                public static DataTable GetOrg_UnitList(string strWhere, int currentPage, int pagesize, out int recordcount)
        {           
            recordcount = 0;

                                                                        
                                    
                        
            return null;
        }

                                                public static DataTable GetT_s_Employee_DeptList(string empID)
        {
            DataTable dt = null;
            DataSet ds = null;

            string strSql = "  select * from zldc_t_s_dept where OrgID in( select DeptID from t_s_Employee_Dept  where EmpID='" + empID + "') ";

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

                                                public static void InsertT_s_Employee_Dept(string EmpID, string DeptID,string strType)
        {
            string strSql = " Insert into T_s_Employee_Dept(EmpID,DeptID,ModelStr) values('" + EmpID + "','" + DeptID + "','"+strType+"')";

            using (_Database db = new _Database("estate"))
            {
                db.ExecuteSql(strSql);
            }
        }

                                                public static DataTable GetMenuByMdl(string Mdl)
        {
            DataTable dt = null;
            DataSet ds = null;

            string strSql = "select distinct menu from zldc_t_s_pdm where mdl='" + Mdl + "' and isMenu=1 and IsActive=1 ";

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }

                                                  public static DataTable GetPdmByMenu(string Menu)
        {
            DataTable dt = null;
            DataSet ds = null;

            string strSql = "select * from zldc_t_s_pdm where menu='"+Menu+"' and isMenu=1 and IsActive=1 order by OrderNo  ";

            using (_Database db = new _Database("estate"))
            {
                ds = db.Query(strSql);
            }

            if (ds != null && ds.Tables.Count > 0)
                dt = ds.Tables[0];

            return dt;
        }
    }
}
